﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using ADODB;
using System.Reflection;

namespace LH.U8API.Tool
{

    /// <summary>
    /// 
    /// </summary>
    public static class DBhelper
    {
        public static ADODB.Connection con; //数据库连接对象

        #region Adobd参数化 方法
        /// <summary>
        /// ConvertRecordSetToDataTable 转换RecordSet DataTabel
        /// </summary>
        /// <param name="recordsetData"></param>
        /// <returns></returns>
        public static DataTable ConvertRs2DTb(Recordset recordsetData)
        {
            //var temp = recordsetData.Fields["ID"].Value;
            try
            {
                DataTable dataTable;
                using (System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter())
                {
                    dataTable = new DataTable();
                    adapter.Fill(dataTable, recordsetData);
                    var suc = dataTable.Rows.Count > 0 ? true : false;
                    return dataTable;
                }
            }
            catch (Exception ex)
            {
                string info = "Atributos: " + recordsetData.Fields.ToString() + " - Cantidad Reg: " + recordsetData.RecordCount.ToString();
                return new DataTable();
            }
        }
        /// <summary>
        /// 获取adodb command对象的参数化
        /// </summary>
        /// <param name="cmd">adodb command对象</param>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static ADODB.Command getAdodbParameters(ADODB.Command cmd, SqlParameter[] ps)
        {
            if (ps.Length == 0) return cmd;
            try
            {
                for (var i = 0; i < ps.Length; i++)
                    cmd.Parameters.Append(cmd.CreateParameter(ps[i].ParameterName,
                 DataTypeEnum.adChar, ParameterDirectionEnum.adParamInput, 100, ps[i].Value));
                return cmd;
            }
            catch (Exception e)
            {
                return cmd;
            }
        }

        public static string getSQLParams(string sql, SqlParameter[] ps, out bool bReplace)
        {
            bReplace = false;
            if (ps.Length == 0) return "FALSE";
            try
            {
                for (var j = 0; j < ps.Length; j++)
                {
                    sql = sql.Replace(ps[j].ParameterName, String.Format("'{0}'", ConvertSql(ps[j].Value.ToString())));
                }
                bReplace = true;
                return sql;
            }
            catch (Exception e)
            {
                return "FALSE:" + e;
            }
        }
        #endregion

        #region ExecuteAdodb 命令
        public static int ExecuteAdodbNonQuery(ADODB.Connection Connection, string safesql)
        {
            object ra = new object();
            ADODB.Command cmd = new ADODB.Command();
            cmd.ActiveConnection = Connection;
            cmd.CommandType = CommandTypeEnum.adCmdText;
            cmd.CommandText = safesql;
            try
            {
                Recordset rs = cmd.Execute(out ra);
                var _ra = (int)ra;
                rs.Close();
                return _ra;
            }
            catch (Exception e)
            {
                throw e;
                //var ex = e.ToString();
                //return 0;
            }
        }
        public static int ExecuteAdodbNonQuery(ADODB.Connection Connection, string sql, params SqlParameter[] values)
        {
            bool suc = false;
            if (values.Length == 0) return 0;
            object ra = new object();

            ADODB.Command cmd = new ADODB.Command();
            cmd.ActiveConnection = Connection;
            cmd.CommandType = CommandTypeEnum.adCmdText;
            cmd.CommandText = getSQLParams(sql, values, out suc);
            if (!suc) return 0; else suc = false;
            try
            {
                Recordset rs = cmd.Execute(out ra);
                var _ra = (int)ra;
                rs.Close();
                return _ra;
            }
            catch (Exception e)
            {
                //var ex = e.ToString();
                //return 0;
                throw e;
            }
        }
        public static DataTable ExecuteAdodb(ADODB.Connection Connection, string safesql)
        {
            object ra = new object();
            ADODB.Command cmd = new ADODB.Command();
            cmd.ActiveConnection = Connection;
            cmd.CommandType = CommandTypeEnum.adCmdText;
            cmd.CommandText = safesql;
            try
            {
                Recordset rs = cmd.Execute(out ra);
                var _ra = (int)ra;
                DataTable dt = ConvertRs2DTb(rs);
                rs.Close();
                return dt;
            }
            catch (Exception e)
            {
                //var ex = e.ToString();
                //return new DataTable();
                throw e;
            }
        }

        public static DataTable ExecuteAdodb(ADODB.Connection Connection, string sql, params SqlParameter[] values)
        {
            bool suc = false;
            if (values.Length == 0) return new DataTable();
            object ra = new object();

            ADODB.Command cmd = new ADODB.Command();
            cmd.ActiveConnection = Connection;
            cmd.CommandType = CommandTypeEnum.adCmdText;
            cmd.CommandText = getSQLParams(sql, values, out suc);
            if (!suc) return new DataTable(); else suc = false;

            try
            {
                Recordset rs = cmd.Execute(out ra);
                var _ra = (int)ra;
                DataTable dt = ConvertRs2DTb(rs);
                rs.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static MSXML2.DOMDocumentClass ExecuteAdodbXML(ADODB.Connection Connection, string sql)
        {
            object ra = new object();
            ADODB.Command cmd = new ADODB.Command();
            cmd.ActiveConnection = Connection;
            cmd.CommandType = CommandTypeEnum.adCmdText;
            cmd.CommandText = sql;
            try
            {
                MSXML2.DOMDocumentClass dom = new MSXML2.DOMDocumentClass();
                Recordset rs = cmd.Execute(out ra);
                var _ra = (int)ra;
                rs.Save(dom, PersistFormatEnum.adPersistXML);
                rs.Close();
                return dom;
            }
            catch (Exception e)
            {
                //var ex = e.ToString();
                //return new DataTable();
                throw e;
            }
        }
        #endregion

        #region  防止SQL 注入式攻击

        /// <summary>
        /// 防止SQL 注入式攻击
        /// </summary>
        /// <param name="inputString"></param>
        /// <returns></returns>
        public static string ConvertSql(string inputString)
        {
            inputString = inputString.Trim();
            inputString = inputString.Replace("'", "''");
            inputString = inputString.Replace(";--", "#");
            inputString = inputString.Replace("=", "#");
            inputString = inputString.Replace("or", "#");
            inputString = inputString.Replace("OR", "#");
            inputString = inputString.Replace("and", "#");
            inputString = inputString.Replace("AND", "#");
            inputString = inputString.Replace("exec", "#");
            inputString = inputString.Replace("execute", "#");
            inputString = inputString.Replace("insert", "#");
            inputString = inputString.Replace("select", "#");
            inputString = inputString.Replace("delete", "#");
            inputString = inputString.Replace("update", "#");
            inputString = inputString.Replace("alter", "#");
            inputString = inputString.Replace("create", "#");
            inputString = inputString.Replace("drop", "#");
            inputString = inputString.Replace("*", "#");
            inputString = inputString.Replace("chr", "#");
            inputString = inputString.Replace("char", "#");
            inputString = inputString.Replace("asc", "#");
            inputString = inputString.Replace("char", "#");
            inputString = inputString.Replace("mid", "#");
            inputString = inputString.Replace("substring", "#");
            inputString = inputString.Replace("master", "#");
            inputString = inputString.Replace("truncate", "#");
            inputString = inputString.Replace("declare", "#");
            inputString = inputString.Replace("xp_cmdshell", "#");
            inputString = inputString.Replace("restore", "#");
            inputString = inputString.Replace("backup", "#");
            return inputString;
        }

        #endregion

        #region DataTable转实体
        /// <summary>
        /// DataTable转实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ConvertToModel<T>(DataTable dt)where T:new()
        {
            List<T> list = new List<T>();
            Type type = typeof(T);
            string tempName = "";
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                PropertyInfo[] propertys = t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;
                    if (dt.Columns.Contains(tempName))
                    {
                        if (!pi.CanWrite) continue;
                        object value = dr[tempName];
                        if (value != DBNull.Value)
                            pi.SetValue(t, value.ToString(), null);
                    }
                }
                list.Add(t);
            }
            return list;
        }

        #endregion
    }
}
